package com.example.mapper.datastatistics;

import com.example.bean.datastatistics.yanglaofuwuchuangwei.YangLaoFuChuangWeiDuoTiaoJian;
import com.example.bean.datastatistics.yanglaofuwuchuangwei.YangLaoFuWuChuangWei;
import com.example.bean.datastatistics.yanglaofuwujigou.YangLaoFuWuJiGou;
import com.example.bean.datastatistics.yanglaofuwujigou.YangLaoFuWuJiGouDuoTiaoJian;
import com.example.bean.datastatistics.yanglaofuwurenqun.YangLaoFuWuRenQun;
import com.example.bean.datastatistics.yanglaofuwurenqun.YangLaoFuWuRenQunDuoTiaoJian;
import com.example.bean.datastatistics.yanglaofuwusheshi.YangLaoFuWuSheShi;
import com.example.bean.datastatistics.yanglaofuwusheshi.YangLaoFuWuSheShiDuoTiaoJian;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface DatastatisticsMapper {

//    @Select("select * from t_basicinformation")
    //第一部分取表格值(多条件)
    List<YangLaoFuWuJiGou> one(YangLaoFuWuJiGouDuoTiaoJian yangLaoFuWuJiGouDuoTiaoJian);
    //第二部分取表格值(正常取值)
    @Select("select credit_code from t_basicinformation where institutions_nature='民办养老机构' and jigou_name=#{jigou_name} GROUP BY credit_code")
    YangLaoFuWuJiGou yanglaofuwujigou(String jigou_name);

    //模块二
//    @Select("SELECT rijianzhaoliao.xingzhengquhua,rijianzhaoliao.jigou_id,count(*) \n" +
//            "AS'rijianzhaoliao_zhongxin',\n" +
//            "(SELECT COUNT(*) FROM safety_system WHERE rijianzhaoliao.jigou_id=safety_system.jigou_id) \n" +
//            "AS 'jujiayanglao_zhongxin' FROM \n" +
//            "rijianzhaoliao LEFT JOIN safety_system ON rijianzhaoliao.jigou_id=safety_system.jigou_id\n" +
//            "GROUP BY jigou_id\n")
    List<YangLaoFuWuSheShi> yanglaofuwusheshiList(YangLaoFuWuSheShiDuoTiaoJian yangLaoFuWuSheShiDuoTiaoJian);

//    @Select("SELECT zhuzhi,count(*) AS 'zong_renshu' FROM ruzhuguanli GROUP BY zhuzhi")
    List<YangLaoFuWuRenQun> yanglaofuwurenqunList(YangLaoFuWuRenQunDuoTiaoJian yangLaoFuWuRenQunDuoTiaoJian);


    @Select("SELECT COUNT(*)AS'rijianzhaoliao' from rijianzhaoliao LEFT JOIN t_basicinformation\n" +
            " ON rijianzhaoliao.jigou_id=t_basicinformation.noid WHERE xingzhengquhua=#{xingzhengquhua}" +
            " AND jigou_name=#{jigouName} AND flag='0'")
    String rijianzhaoliao(YangLaoFuWuSheShi yangLaoFuWuSheShi);

    @Select("SELECT COUNT(*)AS'jujiayanglao' from rijianzhaoliao LEFT JOIN t_basicinformation\n" +
            " ON rijianzhaoliao.jigou_id=t_basicinformation.noid WHERE xingzhengquhua=#{xingzhengquhua}" +
            " AND jigou_name=#{jigouName} AND flag='1'")
    String jujiayanglao(YangLaoFuWuSheShi yangLaoFuWuSheShi);


    @Select("SELECT count(*)  AS 'liuwu_zongrenshu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>65 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='自理（完全自理）') AS 'liuwu_zilishu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>65 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='介助（半自理）') AS 'liuwu_banzilishu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>65 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='介护（完全不能自理）') AS 'liuwu_shinengshu' \n" +
            "FROM ruzhuguanli where (2022-(LEFT(chusheng,4)))>65 AND zhuzhi=#{zhuzhi}")
    YangLaoFuWuRenQun yanglaoyuanfuwurenquna (String zhuzhi);

    @Select("SELECT count(*)  AS 'bawu_zongshu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>80 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='自理（完全自理）') AS 'bawu_zilishu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>80 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='介助（半自理）') AS 'bawu_banlizishu',\n" +
            "(select count(*) FROM ruzhuguanli WHERE (2022-(LEFT(chusheng,4)))>80 \n" +
            "AND zhuzhi=#{zhuzhi} AND shenti='介护（完全不能自理）') AS 'bawu_shinengshu' \n" +
            "FROM ruzhuguanli where (2022-(LEFT(chusheng,4)))>80 AND zhuzhi=#{zhuzhi}")
    YangLaoFuWuRenQun winniepooh (String zhuzhi);

    @Select("SELECT (select count(*) FROM ruzhuguanli WHERE leibie='城市特困人员（城市特困人员（三无））' AND zhuzhi=#{zhuzhi}  \n" +
            "\n" +
            ") AS 'chengshi_sanwushu' FROM ruzhuguanli  WHERE zhuzhi=#{zhuzhi} GROUP BY zhuzhi")
    YangLaoFuWuRenQun heiboteruica(String zhuzhi);

//    @Select("SELECT administrative_division,count(total_number) \n" +
//            "AS 'num_chuangshu' FROM t_basicinformation GROUP BY administrative_division")
    List<YangLaoFuWuChuangWei> yanglaofuwuchuangweiList(YangLaoFuChuangWeiDuoTiaoJian yangLaoFuChuangWeiDuoTiaoJian);


    @Select("\n" +
            "SELECT \n" +
            "(SELECT sum(total_number) FROM t_basicinformation WHERE institutions_nature='公办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS'gongban_num',\n" +
            "(SELECT sum(older_total_number+remnant_total_number+nonage_total_number) FROM t_basicinformation WHERE institutions_nature='公办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS 'gongban_hulixingchuangwei',\n" +
            "(SELECT (sum(total_number)-(sum(older_total_number+remnant_total_number+nonage_total_number))) FROM t_basicinformation WHERE institutions_nature='公办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS 'gongban_putongxingchuangwei',\n" +
            "(SELECT sum(total_number) FROM t_basicinformation WHERE institutions_nature='民办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS'minban_num',\n" +
            "(SELECT sum(older_total_number+remnant_total_number+nonage_total_number) FROM t_basicinformation WHERE institutions_nature='民办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS 'minban_hulixingchuangwei',\n" +
            "(SELECT (sum(total_number)-(sum(older_total_number+remnant_total_number+nonage_total_number))) FROM t_basicinformation WHERE institutions_nature='民办养老机构' AND administrative_division=#{chuanru})\n" +
            " AS 'minban_putongxingchuangwei'\n" +
            "\n" +
            " \n" +
            "FROM t_basicinformation WHERE administrative_division=#{chuanru} GROUP BY administrative_division")
    YangLaoFuWuChuangWei yanglaofuwuchuangweiBean(String chuanru);
}